﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Data.OleDb;

    /// <summary>
    /// 用于获取SqlDataReader的委托
    /// </summary>
    /// <param name="sqlDataReader">返回过来的DataReader</param>
    public delegate void GetDataReaderDelegate(SqlDataReader sqlDataReader);


    /// <summary>
    /// 提供各种数据库操作的SqlHelper类
    /// </summary>
    public class SqlHelper
    {
        //数据连接对象
        private SqlConnection sqlConn = null;
        //OLE数据连接对象
        private OleDbConnection sqlOLEConn = null;
        private string sqlConnStr;

        #region 初始化以及赋值
        public SqlHelper()
        { }

        /// <summary>
        /// 通过连接字符串创建SqlHelper对象
        /// </summary>
        /// <param name="connectiongStr">SqlConnection的连接字符串</param>
        public SqlHelper(string connectiongStr)
        {
            sqlConnStr = connectiongStr;
        }
        /// <summary>
        /// 通过连接字符串创建OLESqlHelper对象
        /// </summary>
        /// <param name="connectiongStr">SqlConnection的连接字符串</param>
        public SqlHelper(string connectiongStr,int a)
        {
            sqlConnStr = connectiongStr;
        }
        /// <summary>
        /// 获取或者设置SqlHelper类的Connection对象
        /// </summary>
        //public SqlConnection SqlConnectionObject
        //{
        //    get { return sqlConn; }
        //    set { sqlConn = value; }
        //}

        /// <summary>
        /// 获取或者设置连接字符串，可能引发Exception异常
        /// </summary>
        public string SqlConnectionStr
        {
            get { return sqlConnStr; }
            set { sqlConnStr = value; }
        }

        #endregion


        #region 返回第一行的第一列
        /// <summary>
        /// 获取第一行第一列的值
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public string ExecuteScalar(SqlCommand sqlCmd)
        {
            lock (this)
            {
                try
                {
                    sqlConn = new SqlConnection(sqlConnStr);
                    sqlCmd.Connection = sqlConn;
                    if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
                    return sqlCmd.ExecuteScalar().ToString();
                }
                catch (Exception)
                {
                    return "";
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                        sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
        }

        #endregion


        #region 获取DataTable方法
        /// <summary>
        /// 获取一个DataTable对象，根据SqlCommand
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public DataTable GetDataTable(SqlCommand sqlCmd)
        {
            lock (this)
            {
                try
                {
                    sqlConn = new SqlConnection(sqlConnStr);
                    sqlCmd.Connection = sqlConn;
                    SqlDataAdapter sqld = new SqlDataAdapter(sqlCmd);
                    DataTable dt = new DataTable();
                    sqld.Fill(dt);
                    return dt;
                }
                catch (Exception)
                {
                    return null;
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                        sqlConn.Close();
                    sqlConn.Dispose();
                }
            }

        }

        /// <summary>
        /// 获取一个DataTable对象，根据表名
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string tableName)
        {
            string sql = string.Format("select * from {0}", tableName);
            SqlCommand sqlc = new SqlCommand();
            sqlc.CommandText = sql;
            return GetDataTable(sqlc);
        }

        /// <summary>
        /// 获取一个DataTable对象，根据表名
        /// </summary>
        /// <param name="sqlCmd">所要展示的列</param>
        /// <returns></returns>
        public DataTable GetDataTable(string tableName, string columnName)
        {
            string sql = string.Format("select {0} from {1}", columnName, tableName);
            SqlCommand sqlc = new SqlCommand();
            sqlc.CommandText = sql;
            return GetDataTable(sqlc);
        }
        #endregion

        #region 获取OLE DataTable方法
        /// <summary>
        /// 获取一个DataTable对象，根据OLECommand,超时时间设置为3分钟
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public DataTable GetOLEDataTable(OleDbCommand sqlCmd)
        {
            lock (this)
            {
                try
                {
                    sqlOLEConn = new OleDbConnection(sqlConnStr);
                    sqlCmd.Connection = sqlOLEConn;
                    sqlCmd.CommandTimeout = 180;
                    OleDbDataAdapter sqld = new OleDbDataAdapter(sqlCmd);
                    DataTable dt = new DataTable();
                    sqld.Fill(dt);
                    return dt;
                }
                catch (Exception)
                {
                    return null;
                }
                finally
                {
                    if (sqlOLEConn.State == ConnectionState.Open)
                        sqlOLEConn.Close();
                    sqlOLEConn.Dispose();
                }
            }

        }

        /// <summary>
        /// 获取一个DataTable对象，根据表名
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetOLEDataTable(string tableName)
        {
            string sql = string.Format("select * from {0}", tableName);
            OleDbCommand sqlc = new OleDbCommand();
            sqlc.CommandText = sql;
            return GetOLEDataTable(sqlc);
        }

        /// <summary>
        /// 获取一个DataTable对象，根据表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public DataTable GetOLEDataTable(string tableName, string columnName)
        {
            string sql = string.Format("select {0} from {1}", columnName, tableName);
            OleDbCommand sqlc = new OleDbCommand();
            sqlc.CommandText = sql;
            return GetOLEDataTable(sqlc);
        }
        #endregion


        #region 执行SQL语句方法
        /// <summary>
        /// 执行Sql语句，返回影响的行数
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public int ExecuteSql(SqlCommand sqlCmd)
        {
            lock (this)
            {
                try
                {
                    sqlConn = new SqlConnection(sqlConnStr);
                    sqlCmd.Connection = sqlConn;
                    if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    return 0;
                }
                finally
                {
                    if (sqlConn.State == ConnectionState.Open)
                        sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
        }

        /// <summary>
        /// 执行Sql语句，返回影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql)
        {
            SqlCommand sqlc = new SqlCommand();
            sqlc.CommandText = sql;
            return ExecuteSql(sqlc);
        }

        #endregion


        #region 获取SqlDataReader对象方法

        /// <summary>
        /// 获取一个SqlDataReader，根据SqlCommand对象
        /// </summary>
        /// <param name="sqlCmd">SqlCommand对象</param>
        /// <param name="dataReaderDelegate">参数为SqlDataReader的委托对象</param>
        public void GetDataReader(SqlCommand sqlCmd, GetDataReaderDelegate dataReaderDelegate)
        {
            try
            {
                sqlConn = new SqlConnection(sqlConnStr);
                sqlCmd.Connection = sqlConn;
                if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
                SqlDataReader sqldr = sqlCmd.ExecuteReader();
                dataReaderDelegate(sqldr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
                sqlConn.Dispose();
            }
        }

        /// <summary>
        /// 获取一个SqlDataReader，根据Sql语句
        /// </summary>
        /// <param name="sqlCmd">Sql语句</param>
        /// <param name="dataReaderDelegate">参数为SqlDataReader的委托对象</param>
        public void GetDataReader(string sql, GetDataReaderDelegate dataReaderDelegate)
        {
            SqlCommand sqlc = new SqlCommand();
            sqlc.CommandText = sql;
            GetDataReader(sqlc, dataReaderDelegate);
        }

        #endregion


        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sqlCmd"></param>
        public void ExecutePro(SqlCommand sqlCmd)
        {
            try
            {
                sqlConn = new SqlConnection(sqlConnStr);
                sqlCmd.Connection = sqlConn;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                    sqlConn.Close();
                sqlConn.Dispose();
            }
        }

        #endregion

        //public DataTable GetPageDataTable
        //    (string tableName, string primaryKey, int pageRowCounts, int pageIndex, out int totalPage)
        //{
        //    string sql = string.Format("");
        //}


        public static string Encrypt(string pToEncrypt, string sKey)
        {
            using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())
            {
                byte[] inputByteArray = Encoding.UTF8.GetBytes(pToEncrypt);
                des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
                des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                using (CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(inputByteArray, 0, inputByteArray.Length);
                    cs.FlushFinalBlock();
                    cs.Close();
                }
                string str = Convert.ToBase64String(ms.ToArray());
                ms.Close();
                return str;
            }
        }
        public static string Decrypt(string pToDecrypt, string sKey)
        {
            byte[] inputByteArray = Convert.FromBase64String(pToDecrypt);
            using (DESCryptoServiceProvider des = new DESCryptoServiceProvider())
            {
                des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
                des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                using (CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write))
                {
                    cs.Write(inputByteArray, 0, inputByteArray.Length);
                    cs.FlushFinalBlock();
                    cs.Close();
                }
                string str = Encoding.UTF8.GetString(ms.ToArray());
                ms.Close();
                return str;
            }
        }

        /// <summary>
        /// 从Excel读取数据
        /// </summary>
        /// <param name="Path">路径</param>
        /// <param name="sheet">表</param>
        /// <param name="field">查询条件</param>
        /// <param name="columns">字段</param>
        /// <returns></returns>
        public static DataTable ExcelToDT(string Path, string sheet, string field, string columns)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=YES;imex=1';";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataTable dt = null;
                strExcel = "select {0} from [{1}$]";
                if (field.Trim().Length > 0)
                    strExcel = strExcel + " where " + field;
                myCommand = new OleDbDataAdapter(string.Format(strExcel, columns, sheet), strConn);
                dt = new DataTable();
                myCommand.Fill(dt);
                return dt;
            }
        }

        /// <summary>
        /// 获取excel的修改命令对象
        /// </summary>
        /// <param name="path">路径</param>
        /// <returns></returns>
        public static OleDbCommand UpdateExcel(string path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=YES;imex=2';";
            OleDbConnection od = new OleDbConnection(strConn);
            OleDbCommand oc = new OleDbCommand();
            oc.Connection = od;
            return oc;
        }
    }
